﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Input;
using DSPL.Contracts.Command;
using DSPL.Contracts.Common;
using DSPL.Contracts.Interfaces;
using DSPL.Contracts.ViewModel;
using Microsoft.Practices.Unity;
using DSPL.Contracts;
using System.Windows;
using System.Globalization;
using System.Diagnostics;
using System.Reflection;
using System.Linq.Expressions;

namespace DSPL.Common.ViewModel
{
    public class ListWindowViewModel : BaseViewModel
    {
        public ListWindowViewModel()
        {
            ResolveDependencies();
        }

        void ResolveDependencies()
        {
            //IUnityContainer container = GlobalVariables.GlobalUnityContainer;
            //if (container != null)
            //{
            //    MasterReadService = container.Resolve<IMasterReadService>();
            //    MasterWriteService = container.Resolve<IMasterWriteService>();
            //    ColumnWithValue = container.Resolve<IColumnWithValue>();
            //    CommonValidations = container.Resolve<ICommonValidations>();
            //    MessageService = container.Resolve<IMessageService>();
            //} 
        }

        public ListWindowViewModel(string tableName)
        {
            ResolveDependencies();
            TableName = tableName;
            Initialize();
        }

        //IMasterReadService MasterReadService { get; set; }
        //IMasterWriteService MasterWriteService { get; set; }
        //IColumnWithValue ColumnWithValue { get; set; }
        //ICommonValidations CommonValidations { get; set; }
        //IMessageService MessageService { get; set; }

        public string TableName { get; set; }
        public string AlternativeTableName { get; set; }
        public HelpNames HelpName { get; set; }
        public HelpNames AlternativeHelpName { get; set; }
        public string FilterColumn { get; set; }
        public object FilterValue { get; set; }
        public string FilterType { get; set; }
        public QComparison FilterEquation { get; set; }
        public string FilterString { get; set; }
        public Modes FormMode { get; set; }
        public DataTable DtMain { get; set; }

        private DataTable dtAllMasterData;
        private DataTable dtDatGridData;
        private DataTable dtData;
        private DataTable dtCopiedData;
        private DataTable dtFilteredData;
        private string labelText;
        int i = 0;
        private Dictionary<string, string> columnList;
        private string columnName;
        private string searchText;
        private string formatToolTip;
        private int pageIndex;
        private int start;
        private int end;
        private int totalItems;
        private DataRow selectedRow;
        Window parentWindow;
        private int rowCount;
        private bool isFirst;
        private bool isLast;

        
        string oldColumnName;

        public int PageIndex
        {
            get { return pageIndex; }
            set
            {
                pageIndex = value;
                OnPropertyChanged("PageIndex");
            }
        }

        public int Start
        {
            get { return start; }
            set
            {
                start = value;
                OnPropertyChanged("Start");
            }
        }

        public int End
        {
            get { return end; }
            set
            {
                end = value;
                OnPropertyChanged("End");
            }
        }

        public int TotalItems
        {
            get { return totalItems; }
            set
            {
                totalItems = value;
                OnPropertyChanged("TotalItems");
            }
        }

        public string LabelText
        {
            get { return labelText; }
            set
            {
                labelText = value;
                OnPropertyChanged("LabelText");
            }
        }

        public DataTable DtDatGridData
        {
            get { return dtDatGridData; }
            set
            {
                dtDatGridData = value;
                OnPropertyChanged("DtAllMasterData");
            }
        }

        public Dictionary<string, string> ColumnList
        {
            get { return columnList; }
            set
            {
                if (columnList == null)
                    return;
                columnList = value;
                OnPropertyChanged("ColumnList");
            }
        }

        public string ColumnName
        {
            get { return columnName; }
            set
            {
                columnName = value;
                OnPropertyChanged("ColumnName");
                //SearchText = "";
                //SearchTextChangedExecuted("");
                //if (!string.IsNullOrEmpty(ColumnName) && !string.IsNullOrWhiteSpace(ColumnName))
                //SetToolTip();
            }
        }
        
        public string SearchText
        {
            get { return searchText; }
            set
            {
                searchText = value;
                OnPropertyChanged("SearchText");
            }
        }
        
        public string FormatToolTip
        {
            get { return formatToolTip; }
            set
            {
                formatToolTip = value;
                OnPropertyChanged("FormatToolTip");
            }
        }

        public DataRow SelectedRow
        {
            get { return selectedRow; }
            set
            {
                selectedRow = value;
                OnPropertyChanged("SelectedRow");
            }
        }
        
        public int RowCount
        {
            get { return rowCount; }
            set { rowCount = value; }
        }

        public bool IsFirst
        {
            get { return isFirst; }
            set
            {
                isFirst = value;
                OnPropertyChanged("IsFirst");
            }
        }

        public bool IsLast
        {
            get { return isLast; }
            set
            {
                isLast = value;
                OnPropertyChanged("IsLast");
            }
        }  

        private ICommand formLoad;
        private ICommand firstClick;
        private ICommand previousClick;
        private ICommand nextClick;
        private ICommand lastClick;
        private ICommand columnSort;
        private ICommand searchTextChanged;
        private ICommand gridDoubleClick;
        private ICommand enterKeyPress;
        private ICommand closeCommand;

        public ICommand FormLoad
        {
            get
            {
                if (this.formLoad == null)
                    this.formLoad = new RelayCommand(FormLoadExecuted);
                return this.formLoad;
            }
            set { formLoad = value; }
        }

        public ICommand FirstClick
        {
            get
            {
                if (this.firstClick == null)
                    this.firstClick = new RelayCommand(FirstClickExecuted);
                return this.firstClick;
            }
            set { firstClick = value; }
        }

        public ICommand PreviousClick
        {
            get
            {
                if (this.previousClick == null)
                    this.previousClick = new RelayCommand(PreviousClickExecuted);
                return this.previousClick;
            }
            set { previousClick = value; }
        }

        public ICommand NextClick
        {
            get
            {
                if (this.nextClick == null)
                    this.nextClick = new RelayCommand(NextClickExecuted);
                return this.nextClick;
            }
            set { nextClick = value; }
        }

        public ICommand LastClick
        {
            get
            {
                if (this.lastClick == null)
                    this.lastClick = new RelayCommand(LastClickExecuted);
                return this.lastClick;
            }
            set { lastClick = value; }
        }

        public ICommand ColumnSort
        {
            get
            {
                if (this.columnSort == null)
                    this.columnSort = new RelayCommand(ColumnSortExecuted);
                return this.columnSort;
            }
            set { columnSort = value; }
        }

        public ICommand SearchTextChanged
        {
            get
            {
                if (this.searchTextChanged == null)
                    this.searchTextChanged = new RelayCommand(SearchTextChangedExecuted);
                return this.searchTextChanged;
            }
            set { searchTextChanged = value; }
        }

        public ICommand GridDoubleClick
        {
            get
            {
                if (this.gridDoubleClick == null)
                    this.gridDoubleClick = new RelayCommand(GridDoubleClickExecuted);
                return this.gridDoubleClick;
            }
            set { gridDoubleClick = value; }
        }

        public ICommand EnterKeyPress
        {
            get
            {
                if (this.enterKeyPress == null)
                    this.enterKeyPress = new RelayCommand(EnterKeyPressExecuted);
                return this.enterKeyPress;
            }
            set { enterKeyPress = value; }
        }        

        public ICommand CloseCommand
        {
            get
            {
                if (this.closeCommand == null)
                    this.closeCommand = new RelayCommand(CloseCommandExecuted);
                return this.closeCommand;
            }
            set { closeCommand = value; }
        }

        private void FormLoadExecuted(object obj)
        {
            if (obj != null)
            {
                parentWindow = (Window)obj;
                parentWindow.Title = "Search Options";
            }
        }

        private void FirstClickExecuted(object obj)
        {
            PageIndex = 0;
            Start = 0;
            End = TotalItems < GlobalVariables.NumberofRecodsPerPage ? TotalItems : Start + GlobalVariables.NumberofRecodsPerPage;
            RefreshData();
            IsFirst = true;
            IsLast = false;
        }

        private void PreviousClickExecuted(object obj)
        {
            --PageIndex;
            End = Start;
            Start = Start == 0 ? 0 : (Start - GlobalVariables.NumberofRecodsPerPage < 0 ? 0 : Start - GlobalVariables.NumberofRecodsPerPage);
            RefreshData();
            IsFirst = (Start == 0);
            IsLast = !(Start == 0);
        }

        private void NextClickExecuted(object obj)
        {
            ++PageIndex;
            Start = End;
            End = TotalItems < GlobalVariables.NumberofRecodsPerPage ? TotalItems : (End + GlobalVariables.NumberofRecodsPerPage > TotalItems ? TotalItems : End + GlobalVariables.NumberofRecodsPerPage);
            RefreshData();
            IsFirst = !(End == TotalItems);
            IsLast = (End == TotalItems);
        }

        private void LastClickExecuted(object obj)
        {
            PageIndex = (TotalItems / GlobalVariables.NumberofRecodsPerPage);
            if (TotalItems > 0)
                Start = (TotalItems / GlobalVariables.NumberofRecodsPerPage) * GlobalVariables.NumberofRecodsPerPage;
            else
                Start = 0;
            End = TotalItems;
            RefreshData();
            IsFirst = false;
            IsLast = true;
        }

        private void ColumnSortExecuted(object obj)
        {
            if (dtCopiedData != null)
            {
                if (oldColumnName != obj.ToString())
                    i = 0;
                else
                    i += 1;
                if (i % 2 == 0)
                    dtCopiedData.DefaultView.Sort = obj.ToString() + " ASC";
                else
                    dtCopiedData.DefaultView.Sort = obj.ToString() + " DESC";

                dtCopiedData = dtCopiedData.DefaultView.ToTable();
                oldColumnName = obj.ToString();
                RefreshData();
            }
        }

        private void SearchTextChangedExecuted(object obj)
        {
            if (obj == null)
                return;
            if (obj.ToString().Trim() != "'")
                FilterColumns(obj.ToString().Trim());
           
            RefreshData();
        }

        private void GridDoubleClickExecuted(object obj)
        {
            var drv = obj as DataRowView;
            DataRow dr = drv.Row;
            DataTable dt = dtAllMasterData;
            string s = dr.Table.Columns[0].ColumnName + " = '" + dr[0].ToString() + "'";
            dt = dt.Select(s).CopyToDataTable();
            SelectedRow = dt.Rows[0];
            if (parentWindow != null)
                parentWindow.Close();
        }

        private void EnterKeyPressExecuted(object obj)
        {
            var drv = obj as DataRowView;
            DataRow dr = drv.Row;
            DataTable dt = dtAllMasterData;
            string s = dr.Table.Columns[0].ColumnName + " = '" + dr[0].ToString() + "'";
            dt = dt.Select(s).CopyToDataTable();
            SelectedRow = dt.Rows[0];
            if (parentWindow != null)
                parentWindow.Close();
        }

        private void CloseCommandExecuted(object obj)
        {
            var window = obj as Window;
            if (window != null)
                window.Close();
        }

        private void SelectDataRowbyFilterValues()
        {
            DataTable dt = dtAllMasterData;
            string s = "";
            if (FilterType == "INT")
            {
                s = FilterColumn + " = " + FilterValue.ToString();
            }
            else if (FilterType == "STRING")
            {
                if (FilterEquation == QComparison.EQUALS)
                    s = FilterColumn + " = '" + FilterValue.ToString() + "'";
                else if (FilterEquation == QComparison.LIKE)
                    s = FilterColumn + " like '" + FilterValue.ToString() + "%'";
            }
            DataRow[] dr = dt.Select(s);
            if (dr.Length > 0)
            {
                dtAllMasterData = dr.CopyToDataTable();
                RowCount = dtAllMasterData.Rows.Count;
                if (RowCount == 1)
                    SelectedRow = dtAllMasterData.Rows[0];
            }
            else
            {
                RowCount = 0;
                dtAllMasterData.Clear();
            }
        }

        public void Initialize()
        {
            if (DtMain == null)
                LoadDataTable();
            else
                dtAllMasterData = DtMain;
            RemoveUnwantedColumns();
            dtData = new DataTable();
            dtData = dtCopiedData;
             RefreshData();
            
        }

        private void RefreshData()
        {
            if (dtFilteredData != null)
                if (dtFilteredData.Rows.Count > 0)
                    dtFilteredData.Clear();


            if (DtDatGridData != null)
                if (DtDatGridData.Rows.Count > 0)
                    DtDatGridData.Clear();

            if (dtCopiedData != null && dtCopiedData.Rows.Count > 0)
            {
                TotalItems = dtCopiedData.Rows.Count;
                FormatData();
                Paging();
                FormatColumnHeaders();
                dtDatGridData = new DataTable();
                if (dtFilteredData!=null)
                {
                    dtDatGridData = dtFilteredData;
                    OnPropertyChanged("DtDatGridData");
                }
                else
                {
                    PageIndex = 0;
                    Start = 0;
                    End = 0;
                    TotalItems = 0;
                    IsFirst = false;
                    IsLast = false;
                }
            }
            else
            {
                PageIndex = 0;
                Start = 0;
                End = 0;
                TotalItems = 0;
                IsFirst = true;
                IsLast = true;
            }
            SetLableText();            
            
        }

        private void SetLableText()
        {
            LabelText = "Showing " + (End == 0 ? 0 : Start + 1) + " to " + End + " of " + TotalItems + " entries";
        }

        private void LoadDataTable()
        {
            if (!string.IsNullOrEmpty(TableName))
            {
                if (MasterReadService != null)
                    dtAllMasterData = MasterReadService.GetAllMasterData(TableName);

                if (!string.IsNullOrEmpty(FilterString))
                    dtAllMasterData = dtAllMasterData.Select(FilterString).CopyToDataTable();
                
            }
            else if (HelpName != HelpNames.Default)
            {
                if (MasterReadService != null)
                    dtAllMasterData = MasterReadService.GetAllMasterDataByHelpName(HelpName);

                if (!string.IsNullOrEmpty(FilterColumn) && FilterValue != null)
                {
                    SelectDataRowbyFilterValues();
                    if (RowCount == 0 && (AlternativeHelpName != HelpNames.Default) && FormMode.Equals(Modes.None))
                    {
                        dtAllMasterData = MasterReadService.GetAllMasterDataByHelpName(AlternativeHelpName);
                        SelectDataRowbyFilterValues();
                    }
                    else if (RowCount == 0 && !string.IsNullOrEmpty(AlternativeTableName) && FormMode.Equals(Modes.None))
                    {
                        if (MasterReadService != null)
                        {
                            dtAllMasterData = MasterReadService.GetAllMasterData(AlternativeTableName);
                            SelectDataRowbyFilterValues();
                        }

                    }
                }
            }
        }

        private void RemoveUnwantedColumns()
        {
            columnList = new Dictionary<string, string>();
            if ((TableName != "" && TableName != null) || DtMain!=null)
            {
                dtCopiedData = dtAllMasterData.Copy();

                foreach (DataColumn item in dtAllMasterData.Columns)
                {
                    if (GlobalVariables.MasterInsertIgnoreList.Contains(item.ColumnName.ToUpper()))
                        dtCopiedData.Columns.Remove(item.ColumnName);
                    else
                        columnList.Add(CommonFunctions.GetFormatedColumnName(item.ColumnName), item.ColumnName);
                }
            }
            else
            {
                dtCopiedData = dtAllMasterData.Copy();
                foreach (DataColumn item in dtAllMasterData.Columns)
                    columnList.Add(CommonFunctions.GetFormatedColumnName(item.ColumnName), item.ColumnName);
            }

            columnList.Add("","");

            Start = 0;
            End = dtCopiedData.Rows.Count < GlobalVariables.NumberofRecodsPerPage ? dtCopiedData.Rows.Count : Start + GlobalVariables.NumberofRecodsPerPage;
            IsFirst = true;
            IsLast = false;

        }

        private void Paging()
        {
            var filteredData = from dr in dtCopiedData.AsEnumerable()
                               select dr;

            var pagedData = filteredData.Skip(PageIndex * GlobalVariables.NumberofRecodsPerPage).Take(GlobalVariables.NumberofRecodsPerPage);

            if (pagedData.Count() > 0)
                dtFilteredData = pagedData.CopyToDataTable();
            else
                dtFilteredData = null;
            //if (dtCopiedData == null)
            //    return;

            //if (dtCopiedData.Rows.Count == 0)
            //    return;

            //dtFilteredData = dtCopiedData.Clone();

            //for (int i = Start; i < End; i++)
            //    dtFilteredData.ImportRow(dtCopiedData.Rows[i]);
        }

        private void FormatColumnHeaders()
        {
            if (dtFilteredData != null)
                foreach (DataColumn item in dtFilteredData.Columns)
                    dtFilteredData.Columns[item.ColumnName].Caption = CommonFunctions.GetFormatedColumnName(item.ColumnName);
        }

        private void SetToolTip()
        {
            if (dtDatGridData.Columns[ColumnName].DataType.Name.ToUpper() == "DATETIME")
                FormatToolTip = "Format should be in 'dd/MM/yyyy' format";

        }

        //private void FilterColumns(string filterValue)
        //{
        //    Stopwatch aa = Stopwatch.StartNew();

        //    string selectString = string.Empty;
        //    DataRow[] drCopied = null;
        //    string[] columnDataTypes = { "DOUBLE", "INT16", "INT32", "INT64", "DECIMAL", "DATETIME" };
        //    List<DataColumn> dtcList = new List<DataColumn>();

        //    if (!string.IsNullOrEmpty(ColumnName))
        //    {
        //        if (columnDataTypes.Contains(dtData.Columns[ColumnName].DataType.Name.ToUpper()))
        //        {
        //            DataColumn dtc = new DataColumn("dtcTemp", typeof(string));
        //            dtData.Columns.Add(dtc);
        //            dtcList.Add(dtc);

        //            if (dtData.Columns[ColumnName].DataType.Name.ToUpper() == "DATETIME")
        //            {
        //                foreach (DataRow dr in dtData.Rows)
        //                {
        //                    dr["dtcTemp"] = string.Format("{0:dd/MM/yyyy}", dr[ColumnName]);
        //                }
        //            }
        //            else
        //                dtc.Expression = "Convert(" + ColumnName + ",System.String)";

        //            selectString = dtc.ColumnName + " like '" + filterValue + "%'";
        //        }
        //        else
        //            selectString = ColumnName + " like '" + filterValue + "%'";
        //    }
        //    else
        //    {
        //        foreach (var item in ColumnList)
        //        {
        //            string colName = item.Value;
        //            if (!string.IsNullOrEmpty(colName))
        //            {
        //                if (!string.IsNullOrEmpty(selectString) && !string.IsNullOrWhiteSpace(selectString))
        //                    selectString = selectString + " OR ";

        //                if (columnDataTypes.Contains(dtData.Columns[colName].DataType.Name.ToUpper()))
        //                {
        //                    DataColumn dtc = new DataColumn("dtcTemp" + colName, typeof(string));
        //                    dtData.Columns.Add(dtc);
        //                    dtcList.Add(dtc);

        //                    if (dtData.Columns[colName].DataType.Name.ToUpper() == "DATETIME")
        //                    {
        //                        foreach (DataRow dr in dtData.Rows)
        //                        {
        //                            dr[dtc.ColumnName] = string.Format("{0:dd/MM/yyyy}", dr[colName]);
        //                        }
        //                    }
        //                    else
        //                        dtc.Expression = "Convert(" + colName + ",System.String)";

        //                    selectString = selectString + dtc.ColumnName + " like '" + filterValue + "%'";
        //                }
        //                else
        //                    selectString = selectString + colName + " like '" + filterValue + "%'";
        //            }
        //        }
        //    }
        //    drCopied = dtData.Select(selectString);


        //    //Stopwatch la = Stopwatch.StartNew();           

        //    //var result = dtData.AsEnumerable().AsQueryable().Where(selectString);

        //    //MessageBox.Show(la.ElapsedMilliseconds.ToString());

        //    if (dtcList != null && dtcList.Count > 0)
        //        foreach (DataColumn dtc in dtcList)
        //            dtData.Columns.Remove(dtc);

        //    if (drCopied.Count() > 0)
        //    {
        //        dtCopiedData = drCopied.CopyToDataTable();
        //        Start = 0;
        //        End = dtCopiedData.Rows.Count < GlobalVariables.NumberofRecodsPerPage ? dtCopiedData.Rows.Count : Start + GlobalVariables.NumberofRecodsPerPage;
        //    }
        //    else
        //    {
        //        dtCopiedData = null;
        //        Start = 0;
        //        End = 0;
        //    }
        //    MessageBox.Show(aa.ElapsedMilliseconds.ToString());

        //}

        private void FilterColumns(string filterValue)
        {
            string selectString = string.Empty;
            string[] columnDataTypes = { "DOUBLE", "INT16", "INT32", "INT64", "DECIMAL", "DATETIME" };
            List<DataColumn> dtcList = new List<DataColumn>();
            string columnName = ColumnName;

            if (!string.IsNullOrEmpty(ColumnName))
            {
                if (columnDataTypes.Contains(dtData.Columns[ColumnName].DataType.Name.ToUpper()))
                {
                    DataColumn dtc = new DataColumn("dtcTemp", typeof(string));
                    dtData.Columns.Add(dtc);
                    dtcList.Add(dtc);

                    if (dtData.Columns[ColumnName].DataType.Name.ToUpper() == "DATETIME")
                    {
                        foreach (DataRow dr in dtData.Rows)
                        {
                            dr["dtcTemp"] = string.Format("{0:dd/MM/yyyy}", dr[ColumnName]);
                        }
                    }
                    else
                        dtc.Expression = "Convert(" + ColumnName + ",System.String)";

                    columnName = dtc.ColumnName;
                }

                var query = dtData.AsEnumerable().AsQueryable();
                query = query.Where(datarow => ((string)datarow[columnName]).ToUpper().Contains(filterValue.ToUpper()));

                if (query.Count() > 0)
                    dtCopiedData = query.CopyToDataTable();
                else
                    dtCopiedData = null;
            }
            else
            {
                System.Linq.Expressions.Expression right = null;
                System.Linq.Expressions.Expression left = null;
                System.Linq.Expressions.Expression e1 = null;
                System.Linq.Expressions.Expression predicateBody = null;

                ReadOnlyCollection<System.Linq.Expressions.Expression> collection;
                MethodCallExpression left1;
                MethodCallExpression left2;

                // Retrieve the MethodInfo of the generic Field<String>(this DataRow, String) method
                MethodInfo mi = typeof(DataRowExtensions).GetMethod("Field", new Type[] { typeof(DataRow), typeof(string) });
                mi = mi.MakeGenericMethod(typeof(string));

                List<System.Linq.Expressions.Expression> list = new List<System.Linq.Expressions.Expression>();

                // ParameterExpression   Name: String: “datarow”  NodeType: ExpressionType: “Parameter”  Type: Type: “DataRow”
                ParameterExpression datarow = System.Linq.Expressions.Expression.Parameter(typeof(DataRow), "datarow");

                // ConstantExpression   Value: Object: “ColumnName”  NodeType: ExpressionType: “Constant” Type: Type: “String”
                ConstantExpression constant = null;
                right = System.Linq.Expressions.Expression.Constant(filterValue.ToUpper());

                foreach (var item in ColumnList)
                {
                    string colName = item.Value;
                    if (!string.IsNullOrEmpty(colName))
                    {
                        if (predicateBody != null && e1 != null)
                            predicateBody = System.Linq.Expressions.Expression.Or(predicateBody, e1);
                        else if (e1 != null)
                            predicateBody = e1;

                        list.Clear();

                        if (columnDataTypes.Contains(dtData.Columns[colName].DataType.Name.ToUpper()))
                        {
                            DataColumn dtc = new DataColumn("dtcTemp" + colName, typeof(string));
                            dtData.Columns.Add(dtc);
                            dtcList.Add(dtc);

                            if (dtData.Columns[colName].DataType.Name.ToUpper() == "DATETIME")
                            {
                                foreach (DataRow dr in dtData.Rows)
                                {
                                    dr[dtc.ColumnName] = string.Format("{0:dd/MM/yyyy}", dr[colName]);
                                }
                            }
                            else
                                dtc.Expression = "Convert(" + colName + ",System.String)";

                            constant = System.Linq.Expressions.Expression.Constant(dtc.ColumnName, typeof(string));
                        }
                        else
                            constant = System.Linq.Expressions.Expression.Constant(colName, typeof(string));

                        // Add the Expression into List
                        list.Add(datarow);
                        list.Add(constant);

                        // Copy the List<Expression> elements into ReadOnlyCollection<Expression>
                        collection = new ReadOnlyCollection<System.Linq.Expressions.Expression>(list);

                        // MethodCallExpression
                        //      Method: MethodInfo:  “Field<String>”
                        //      Object: Expression: null
                        //      Arguments: ReadOnlyCollection<Expression>
                        //          ...
                        //      NodeType: ExpressionType: “Call”
                        //      Type: Type: “String”
                        left1 = System.Linq.Expressions.Expression.Call(null, mi, collection);
                        left2 = System.Linq.Expressions.Expression.Call(left1, typeof(string).GetMethod("ToString", System.Type.EmptyTypes));
                        left = System.Linq.Expressions.Expression.Call(left2, typeof(string).GetMethod("ToUpper", System.Type.EmptyTypes));
                        e1 = System.Linq.Expressions.Expression.Call(left, "Contains", null, right);

                    }
                }

                if (predicateBody != null && e1 != null)
                    predicateBody = System.Linq.Expressions.Expression.Or(predicateBody, e1);
                else if (predicateBody != null && e1 != null)
                    predicateBody = e1;

                // The IQueryable data to query.
                //IQueryable<String> queryableData = companies.AsQueryable<string>();
                IQueryable<DataRow> queryableData = dtData.Rows.Cast<System.Data.DataRow>().AsQueryable<DataRow>();

                MethodCallExpression whereCallExpression = System.Linq.Expressions.Expression.Call(
                    typeof(Queryable),
                    "Where",
                    new Type[] { queryableData.ElementType },
                    queryableData.Expression,
                    System.Linq.Expressions.Expression.Lambda<Func<DataRow, bool>>(predicateBody, new ParameterExpression[] { datarow }));

                IQueryable<DataRow> results = queryableData.Provider.CreateQuery<DataRow>(whereCallExpression);
                if (results.Count() > 0)
                    dtCopiedData = results.CopyToDataTable();
            }

            if (dtcList != null && dtcList.Count > 0 && dtCopiedData != null)
            {
                foreach (DataColumn dtc in dtcList)
                {
                    dtData.Columns.Remove(dtc);
                    dtCopiedData.Columns.Remove(dtc.ColumnName);
                }
            }

            if (dtCopiedData.Rows.Count > 0)
            {
                Start = 0;
                End = dtCopiedData.Rows.Count < GlobalVariables.NumberofRecodsPerPage ? dtCopiedData.Rows.Count : Start + GlobalVariables.NumberofRecodsPerPage;
                IsFirst = true;
                IsLast = false;
            }
            else
            {
                dtCopiedData = null;
                Start = 0;
                End = 0;
                IsFirst = false;
                IsLast = false;
            }
        }

        private void FormatData()
        {
            IFormatProvider dateFormat = new CultureInfo("en-GB", true);
            DataTable dtFormatted = dtCopiedData.Copy();

            if (dtCopiedData != null)
                if (dtCopiedData.Rows.Count > 0)
                {
                    foreach (DataColumn dataColumn in dtCopiedData.Columns)
                        if (dataColumn.DataType.Name.ToUpper() == "DATETIME")
                        {
                            DataColumn dtc = new DataColumn(dataColumn.ColumnName, typeof(string));
                            dtc.Caption = dataColumn.Caption;
                            dtFormatted.Columns.Remove(dataColumn.ColumnName);
                            dtFormatted.Columns.Add(dtc);

                            for (int i = 0; i < dtCopiedData.Rows.Count; i++)
                                dtFormatted.Rows[i][dtc] = string.Format("{0:dd/MM/yyyy}", dtCopiedData.Rows[i][dataColumn]);

                        }
                    dtCopiedData = dtFormatted.Copy();
                }

        }

    }
}
